﻿

CREATE PROCEDURE [dbo].[InchideAnFares]  
@pAn as int,
@codUnit as smallint AS
BEGIN

---creaza tabelele corespunzatoare
EXEC CreazaArhivaFares @An = @pAn
DECLARE @strAn varchar(4)
DECLARE @Comanda varchar(5000)
DECLARE @strCodUnitate varchar(40)

SET @strAn=CAST(@pAn AS varchar(4))
SET @strCodUnitate =  CAST(@codUnit AS varchar(20))

BEGIN TRAN 
PRINT('Documente')
--ArticoleAliniate---------------------------------------
SET @Comanda = 'DELETE FROM [DocumentePlataDetaliu'+@strAn+'] WHERE IDDocumentPlata IN (SELECT IDDocumentPlata FROM [DocumentePlata'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate + ')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [DocumenteDetaliu'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [Documente'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [DocumentePlata'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE



SET @Comanda = 'INSERT INTO [Documente'+@strAn+'] SELECT * FROM [Documente] a WHERE a.[CodUnitate]=' + @strCodUnitate + ' AND  YEAR(a.DataDoc)=' +@strAn
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('DocumenteDetaliu')
--Bilant---------------------------------------


SET @Comanda = 'INSERT INTO [DocumenteDetaliu'+@strAn+']  SELECT * FROM [DocumenteDetaliu] WHERE IDDocument IN (SELECT IDDocument FROM [Documente'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate+')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE



PRINT('DocumentePlata')
--ArticoleAliniate---------------------------------------

SET @Comanda = 'INSERT INTO [DocumentePlata'+@strAn+'] SELECT * FROM [DocumentePlata] a WHERE a.[CodUnitate]=' + @strCodUnitate + ' AND  YEAR(a.Data)=' +@strAn
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('DocumentePlataDetaliu')
--Bilant---------------------------------------

SET @Comanda = 'INSERT INTO [DocumentePlataDetaliu'+@strAn+']  SELECT * FROM [DocumentePlataDetaliu] WHERE IDDocument IN (SELECT IDDocument FROM [Documente'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate+')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE




PRINT('Sterge datele arhivate')

SET @Comanda = 'DELETE FROM [DocumentePlataDetaliu] WHERE IDDocument IN (SELECT IDDocument FROM [DocumentePlata'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate+')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [DocumentePlata] WHERE [CodUnitate]=' + @strCodUnitate + ' AND  YEAR(Data)=' +@strAn
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [DocumenteDetaliu] WHERE IDDocument IN (SELECT IDDocument FROM [Documente'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate+')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [Documente] WHERE [CodUnitate]=' + @strCodUnitate + ' AND  YEAR(DataDoc)=' +@strAn
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE




IF EXISTS(SELECT * FROM [Arhive] WHERE [Anul]=@pAn AND [CodUnitate]=@codUnit)
BEGIN
	SET @Comanda = 'UPDATE [Arhive] SET [Fares]=1 WHERE [CodUnitate]=' + @strCodUnitate + 'AND [Anul]=' + @strAn;
END
ELSE
BEGIN
SET @Comanda = 'INSERT INTO [Arhive] ([Denumire],[CodUnitate],[Anul],[Fares])  VALUES
           (''' + @strAn + ''',' + @strCodUnitate + ',' + @strAn + ',1)'
END
	
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE	

SET @Comanda = 'UPDATE [FaresSetup] SET [AnCurent] = [AnCurent] + 1 WHERE [CodUnitate] =' + @strCodUnitate 
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE	


COMMIT

GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare la inchiderea anului!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
Gata:

END